package com.etc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.pojo.CourseInfo;
import com.etc.utils.DBUtil;

public class CourseInfoDaoImpl implements CourseInfoDao {

	List<CourseInfo> list = null;

	@Override
	public List<CourseInfo> queryCourse() {
		list = new ArrayList<>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		   .append(" course_no")
		   .append(" ,course_name")
		   .append(" ,course_score")
		   .append(" from tab_course");
		ResultSet rs = DBUtil.doQuery(sql.toString());

		try {
			while (rs.next()) {

				Integer course_no = rs.getInt("course_no");
				String course_name = rs.getString("course_name");
				float course_score = rs.getFloat("course_score");

				list.add(new CourseInfo(course_no, course_name, course_score));

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return list;
	}
	
	@Override
	public List<CourseInfo> queryCourseBySno(String studentNo) {
		list = new ArrayList<>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		   .append(" course_no")
		   .append(" ,course_name")
		   .append(" ,course_score")
		   .append(" from tab_course")
		   .append(" where course_no not in")
		   .append(" (")
		   .append(" select")
		   .append(" a.course_no")
		   .append(" from tab_score as a")
		   .append(" inner join tab_course as b")
		   .append(" on a.course_no = b.course_no")
		   .append(" where a.student_no = ?")
		   .append(" )");
		ResultSet rs = DBUtil.doQuery(sql.toString(), studentNo);
		try {
			while (rs.next()) {

				Integer course_no = rs.getInt("course_no");
				String course_name = rs.getString("course_name");
				float course_score = rs.getFloat("course_score");

				list.add(new CourseInfo(course_no, course_name, course_score));

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return list;
	}

}
